<?php

class Grid {

    private $html;       //the html code built from the repeater.
    private $pattern;    //pattern to be used as a repeater
    private $tableName;  //tableName. Used for the given generic sql statements
    private $key;         //Key in the current table to be queried for the generic functions
    private $results;    //mysql results from query
    
    //DataBase stuff. Hard-code these variables. No getters and setters.
    private $hostName = "localhost";
    private $userName = "root";
    private $password = "";
    private $dbName = ""; //Database name to connect to for mysql
    public $Link;

    /**
     * Sets the TableName, and the name of the table's Key.
     * @param <type> $tableName
     * @param <type> $key
     */
    function __construct($tableName = "", $key = "") {
        $this->tableName = strtolower($tableName);
        $this->key = $tableName == "" ? $key : $tableName . "Id";

        $this->Connect();
    }

    /**
     *  Inserts an array of values into the table that was set in the
     *  constructor. The key can just be numbers. Only the values in the array
     * matter.
     * @param <type> $values - array to insert
     */
    public function InsertOne($values) {
        $sql = "INSERT INTO $this->tableName VALUES (null"; //null for key
        foreach ($values as $val) {
            $sql.= ", '$val'";
        }
        $sql .= ")";

        $this->ExecuteSQL($sql);
    }

    /**
     *  Inserts one object into the database. The object must be the exactly the same
     *  as the table being inserted into. The variables must appear in the same order, and
     * must be spelled the same.
     * @param <type> $obj - Object to be inserted into database
     */
    public function InsertOneObject($object) {
        $values = get_object_vars($object); //$obj datamembers need to be public for this to work

        $sql = "INSERT INTO $this->tableName VALUES ("; //null for key
        $i = 0;
        foreach ($values as $val) {

            if ($i == 0)
                $sql.= "null";
            else
                $sql.= ", '$val'";
            $i++;
        }
        $sql .= ")";

        $this->ExecuteSQL($sql);
        return $this->GetMostRecentIndex();
    }

    /**
     *  Sets the results of selecting one row from the table
     * @param <type> $id - Id of row to pull from database.
     */
    public function SelectOne($id) {
        $sql = "SELECT * FROM " . $this->tableName . " WHERE " . $this->key . " = '$id'";

        $this->ExecuteSQL($sql);
    }

    /**
     * Sets the results of selecting all of the rows from the table
     */
    public function SelectAll($orderBy = "", $col = "") {
        if ($orderBy == "" || $col == "")
            $sql = "SELECT * FROM " . $this->tableName;
        else
            $sql = "SELECT * FROM " . $this->tableName . " ORDER BY $col $orderBy";

        $this->ExecuteSQL($sql);
    }

    /**
     * Deletes the row of the table with the Id that matches the Id passed in
     * @param <type> $id - Id of row to delete from the database
     */
    public function DeleteOne($id) {
        $sql = "DELETE FROM " . $this->tableName . " WHERE " . $this->key . " = " . $id;

        $this->ExecuteSQL($sql);
    }

    /**
     *  Updates one array into the table.
     * @param <type> $array - array where the keys match the table columns
     * in the exact order of the columns.
     */
    public function UpdateOne($array) {

        $keys = array_keys($array); //keys must match exactly database columns. First key must be the Id Key
        $values = array_values($array);
        $sql = "UPDATE $this->tableName SET ";
        for ($i = 1; $i < count($array); $i++) {
            $sql.="$keys[$i] = '$values[$i]', ";
        }
        $sql = substr($sql, 0, -2);  //Get rid of trailing comma
        $sql.="WHERE $keys[0] = '$values[0]'"; //where Id columns equal

        $this->ExecuteSQL($sql);
    }

    /**
     *  Updates one object into the database. The object must be the exactly the same
     *  as the table being inserted into. The variables must appear in the same order, and
     * must be spelled the same.
     * @param <type> $obj - Object to be inserted into database
     */
    public function UpdateOneObject($obj) {
        $array = get_object_vars($obj); //$obj datamembers need to be public for this to work

        $this->UpdateOne($array);
    }

    /**
     * Binds a results row (array) to an object.
     * 
     * @param $obj Object to assign to
     * @param $row row to assign to Object
     */
    public function RowToObject($obj, $row) {
        $vars = get_object_vars($obj);

        foreach ($vars as $name => $val) {
            $obj->$name = $row[$name];
        }
    }

    /**
     *  - Executes a sql statement.
     * - Can either pass in the custom sql at will, or use the set procedures in this
     * method which will build the sql and then pass it into here.
     * @param <type> $sql - The sql statement to be executed.
     */
    public function ExecuteSQL($sql) {
        $this->executeMySQL($sql);
    }

    /**
     * Connects to mysql database and executes the sql statement against it. Sets
     * $this->results to be used later.
     * @param <type> $sql
     */
    private function executeMySQL($sql) {

        $this->results = mysql_query($sql);

        if ($this->results === false) {
            throw new Exception("The following MySQL query failed: " . $sql . " - " . mysql_error());
        }
    }

    /**
     *
     * @return <type> Integer count of the amount of rows affected by the previous statement.(update, delete, insert)
     */
    public function NumRowsAffected() {
        return mysql_affected_rows();
    }

    /**
     *
     * @return <type> count of the amounts of rows that are in the results.
     */
    public function GetResultsCount() {
        return mysql_num_rows($this->results);
    }

    public function GetMostRecentIndex() {
        return mysql_insert_id();
    }

    /**
     *
     * @return <type> associated array of the next row in the result. (keys match table columns)
     */
    public function FetchNext() {
        return mysql_fetch_assoc($this->results);
    }

    /**
     *
     * @return <type> 2D associated array of all of the results. (keys match table columns)
     */
    public function FetchAll() {
        $array;
        while ($this->results != false && $row = $this->FetchNext()) {
            $array[] = $row;
        }
        return $array;
    }

    /**
     *  Safely escapes a string. Use for user input that can come from a text box,
     * or url.
     * @param <type> $string - string to escape
     * @return <type> escaped string that is safe for database insertion.
     */
    public function Escape($string) {
        mysql_connect($this->hostName, $this->userName, $this->password) or die(mysql_error());
        return mysql_real_escape_string($string);
    }

    public function Connect() {
        $this->Link = mysql_connect($this->hostName, $this->userName, $this->password) or die(mysql_error());
        if ($this->Link === false) {
            throw new Execption("Could not connect to database " . $this->hostName . " : " . mysql_error());
        }

        if ($this->dbName != "")
            mysql_select_db($this->dbName, $this->Link);
    }

    public function BeginTransaction() {
        mysql_query("BEGIN");
    }

    public function CommitTransaction() {
        mysql_query("COMMIT");
    }

    public function RollbackTransaction() {
        mysql_query("ROLLBACK");
    }

    /**
     * Selects a range of row.
     * - currentPage and itemsPerPage must already be set
     */
    public function SelectRange() {
        $from = $this->currentPage * $this->itemsPerPage;
        $to = $from + $this->itemsPerPage;
        $sql = "SELECT * FROM $this->tableName LIMIT $from, $to";
        $this->ExecuteSQL($sql);
    }

    /**
     * 
     * @param string $tableHeader - if set, will go as attribute to table eg. style='...' or id='...' 
     */
    public function BuildResultsView($tableHeader = "") {
        $sections = explode("#", $this->pattern);

        if (count($sections) < 2)
            throw new Exception("Invalid table pattern");

        $this->html = ($tableHeader === false) ? "" : "<table " . $tableHeader . ">";

        while ($row = $this->FetchNext()) {
            for ($i = 0; $i < count($sections); $i+=2) {
                $this->html.= $sections[$i];

                try //in case it ended with data, then there won't be an $i+1 
                {
                    if ($sections[$i + 1] == "edit") {
                        $id = $row[$this->key];
                        $this->html .= "<a href='#' onclick='editRow(\"$id\")'>Edit</a>";
                    } else if ($sections[$i + 1] == "delete") {
                        $id = $row[$this->key];
                        //$this->html .= '<a href="javascript:deleteRow('.$id.');" onclick="return confirm(\'Are you sure\');">Delete</a>';
                        $this->html .= '<a href="javascript:deleteRow(' . $id . ');" onclick="return myConfirm(event, this);" >Delete</a>';
                    } else if ($sections[$i + 1] == "sendEmail") {
                        $id = $row[$this->key];
                        $this->html .= "<a href='#' onclick='emailRow(\"$id\")'>Email</a>";
                    } else {
                        $this->html.= $row[$sections[$i + 1]];
                    }
                } catch (Exception $e) {
                    break;
                }
            }
        }

        $this->html.= ($tableHeader === false) ? "" : "</table>";
    }

    public function GetDropDownList($name, $value, $text) {
        $drop = "<select name='$name' id='$name'>";
        $results = $this->FetchAll();
        if (count($results) > 0) {
            foreach ($results as $row) {
                $drop.="<option value='$row[$value]' >$row[$text]</option>";
            }
        }
        $drop.="</select>";
        return $drop;
    }

    /**
     * 
     * @param unknown_type $name - name of drop down list
     * @param unknown_type $array - the actual options
     * @param unknown_type $onChange - method to call on change
     */
    public function GetDropDownListFromArray($name, $array, $onChange = "") {
        $drop = "<select name='$name' id='$name' onChange='$onChange'>";
        foreach ($array as $key => $value) {
            $drop.="<option name='$key' >$value</option>";
        }
        $drop.="</select>";
        return $drop;
    }

    //GETTERS AND SETTERS
    public function SetPattern($pattern) {
        $this->pattern = $pattern;
    }

    public function SetKey($key) {
        $this->key = $key;
    }

    public function SetTableName($tableName) {
        $this->tableName = $tableName;
    }

    public function SetDBName($dbName) {
        $this->dbName = $dbName;
        mysql_select_db($this->dbName, $this->Link);
    }

    public function GetResults() {
        return $this->results;
    }

    public function SetResults($results) {
        $this->results = $results;
    }

    public function GetHtml() {
        return $this->html;
    }

}

?>